Index¶

CREATE INDEX¶

Creates an index to a specified table by using the CREATE INDEX statement. Regarding writing index name, see Identifier.

For how to use indexes on the SELECT statement like Using SQL Hint, Descending Index, Covering Index, Index Skip Scan, ORDER BY Optimization and GROUP BY Optimization, and how to create Filtered Index and Function-based Index, see Query Optimization.

CREATE [UNIQUE] INDEX index_name ON table_name <index_col_desc> ;

    <index_col_desc> ::=
        ( column_name [ASC | DESC] [ {, column_name [ASC | DESC]} ...] ) [ WHERE <filter_predicate> ]
        | (function_name (argument_list) )

• UNIQUE: Creates an index with unique values.
• index_name: Specifies the name of the index to be created. The index name must be unique in the table.
• table_name: Specifies the name of the table where the index is to be created.
• column_name: Specifies the name of the column where the index is to be applied. To create a composite index, specify two or more column names.
• ASC | DESC: Specifies the sorting order of columns.
• <filter_predicate>: Defines the conditions to create filtered indexes. When there are several comparison conditions between a column and a constant, filtering is available only when the conditions are connected by using AND. Regarding this, definitely watch Filtered Index.
• function_name (argument_list): Defines the conditions to create function-based indexes. Regarding this, definitely watch Function-based Index.

The following example shows how to create a descending index.

CREATE INDEX gold_index ON participant(gold DESC);

The following example shows how to create a multiple column index.

CREATE INDEX name_nation_idx ON athlete(name, nation_code);

ALTER INDEX¶

The ALTER INDEX statement rebuilds an index. In other words, it drops and rebuilds an index. If a table name and column names are added at the end of the ON clause, a new index is recreated with the table and column names.

ALTER [UNIQUE] INDEX index_name
[ON table_name [<index_col_desc>]] REBUILD

<index_col_desc> ::=
    ( column_name [ASC | DESC] [ {, column_name [ASC | DESC]} ...] ) [ WHERE <filter_predicate> ]
    | (function_name (argument_list) )

• UNIQUE: This should be specified if the index to be recreated is an unique index.
• index_name: Specifies the name of the index to be recreated. The index name must be unique in the table.
• table_name: Specifies the name of the table where the index is recreated.
• column_name: Specifies the name of the column where the index is to be applied. To create a composite index, specify two or more column names.
• ASC | DESC: Specifies the sorting order of columns.
• <filter_predicate>: Defines the conditions to create filtered indexes. When there are several comparison conditions between a column and a constant, filtering is available only when the conditions are connected by using AND.
• function_name (argument_list): Defines the conditions to create function-based indexes.

The following is an example of recreating indexes in various ways:

CREATE INDEX i_game_medal ON game(medal);
ALTER INDEX i_game_medal ON game REBUILD;
ALTER INDEX char_idx ON athlete(gender, nation_code) WHERE gender='M' AND nation_code='USA' REBUILD;

DROP INDEX¶

Use the DROP INDEX statement to drop an index.

DROP [UNIQUE] INDEX index_name [ON table_name] ;

• UNIQUE: Specifies that the index to be dropped is a unique index. This also can be dropped with DROP CONSTRAINT clause.
• index_name: Specifies the name of the index to be dropped. If omitted, a name is automatically assigned as i_<table_name>_<column_names>.
• table_name: Specifies the name of the table whose index is dropped.

The following is an example of dropping an index:

DROP INDEX game_date_idx ON game;